;; Load this file

(eval-when (:compile-toplevel :load-toplevel :execute)
  (require :autoproject)
  (require :autoproject.crud)
  (require :clsql-sqlite3)
  (load (merge-pathnames "db-adapter-clsql" *load-truename*)))

(defpackage :autoproject.crud.test
  (:use :cl :autoproject.crud))

(in-package :autoproject.crud.test)

(defvar *here* 
  (make-pathname :directory (pathname-directory *load-truename*)))

(setf *default-pathname-defaults* *here*)

(defvar *db*
  (namestring (merge-pathnames "test-crud.db" *load-truename*)))

(defvar *db-schema*
  (namestring (merge-pathnames "test-1.sql" *load-truename*)))

(defvar *crud*)

(when (probe-file *db*)
  (delete-file *db*))

(asdf::run-shell-command "sqlite3 ~A < ~A" *db* *db-schema*)

(defun test-create-crud ()
  (let ((crud (make-crud 'crud
                         'autoproject.crud::clsql-db-adapter
                         :sqlite3 (list *db*)))
        (file (merge-pathnames "test-crud-crud.lisp")))
    (write-crud-forms-to-file 
     crud "test-crud-crud.lisp"
     "STUDENTS-CRUD" "AUTOPROJECT.CRUD.TEST-CRUD")
    (assert (probe-file file))
    (load (compile-file file))))

(test-create-crud) 
(use-package :autoproject.crud.test-crud)

(setf *crud* (make-crud 'students-crud
                        'autoproject.crud::clsql-db-adapter
                        :sqlite3 (list *db*)))

(define-foreign-key
    (student id) (course-grade student-id) :assign-key t)

(define-foreign-key
    (course-grade student-id) (student id) :assign-key t)

(define-foreign-key
    (course id) (course-grade course-id) :assign-key t)

(define-foreign-key
    (course-grade course-id) (course id) :assign-key t)


(defun get-next-id (table)
  (let ((s (make-select 
            :attr "max(id)+1" 
            :table (autoproject.crud::get-table-name table))))
    (or (caar (crud-select-raw *crud* s)) 
        0)))

(defun save-student (first-name ssn address)
  (let ((student
         (make-instance 'student
                        :id (get-next-id 'student)
                        :first-name first-name
                        :ssn ssn
                        :address address)))
    (crud-create-from-instance *crud* student)
    student))

(defun save-course (name)
  (let ((course (make-instance 'course
                               :id (get-next-id 'course)
                               :name name)))
    (crud-create-from-instance *crud* course)
    course))

(defun match-1 (class &rest args)
  (car (crud-match *crud* (apply #'make-instance class
                                 args))))

(defun match (class &rest args)
  (car (crud-match *crud* (apply #'make-instance class
                                 args))))

(defun resolve-1 (item class)
  (car (resolve-key *crud* item class)))

(defun resolve (item class)
  (resolve-key *crud* item class))

(defun grade-student-1 (student course grade)
  (let ((course-grade 
         (make-instance 'course-grade
                        :id (get-next-id 'course-grade)
                        :student-id (get-id student)
                        :course-id (get-id course)
                        :grade grade)))
    (crud-create-from-instance *crud* course-grade)
    course-grade))

(defun grade-student-2 (student course grade)
  (let* ((course-grade 
          (make-instance 'course-grade
                         :id (get-next-id 'course-grade)
                         :grade grade)))
    #+nil
    (progn
      (crud-create-from-instance *crud* course-grade)
      (assign-key *crud* course-grade student)
      (assign-key *crud* course-grade course)
      course-grade)
    #-nil    
    (autoproject.crud::with-crud (*crud* :transactionp t)
      (crud-create-from-instance *crud* course-grade)
      (assign-key *crud* course-grade student)
      (assign-key *crud* course-grade course)
      course-grade)))

(defun clear-tables ()
  (autoproject.crud::crud-execute-raw 
   *crud* "delete from student")
  (autoproject.crud::crud-execute-raw 
   *crud* "delete from course")
  (autoproject.crud::crud-execute-raw 
   *crud* "delete from course_grade"))
                                      

(defun test-populate-crud ()
  (flet ((grade-1 (student course grade)
           (grade-student-1 (match-1 'student :first-name student)
                            (match-1 'course :name course)
                            grade))
         (grade-2 (student course grade)
           (grade-student-2 (match-1 'student :first-name student)
                            (match-1 'course :name course)
                            grade))
         (student (name)
           (match-1 'student :first-name name)))
    
    (clear-tables)
    (save-student "Joe" "2342" "Joe Street 5")
    (save-student "John" "12345" "John Avenue 5")  
    (save-student "Jane" "89790" "Jane Lane 5")

    (format t "Saved students.~%")

    (save-course "Databases 101")
    (save-course "6.001")
    (save-course "First Aid")

    (format t "Saved courses.~%")

    (grade-1 "Joe" "Databases 101" 4)
    (grade-1 "Joe" "6.001" 1)
    (grade-1 "Joe" "First Aid" 2)    
    
    (grade-2 "Jane" "Databases 101" 2)
    (grade-2 "Jane" "6.001" 1)
    (grade-2 "Jane" "First Aid" 5)
    
    (format t "Saved grades.~%")

    (assert (equal (get-address (match-1 'student :first-name "Joe"))
                   "Joe Street 5"))
    
    (assert (= (length (resolve (student "Joe") 'course-grade))
               3))
    
    (assert (= (length (resolve (student "Jane") 'course-grade))
               3))
    
    (assert (= (length (resolve (student "John") 'course-grade))
               0))

    (assert (= (length (crud-select 
                        *crud*
                        (make-select :table "course_grade")))
               6))
    
    (assert (= (length (crud-select-raw
                        *crud*
                        "select * from course_grade"))
               
               6))
    
    (let* ((db101 (match-1 'course :name "Databases 101"))
           (joe (student "Joe"))
           (grade (make-instance 'course-grade
                                 :course-id (get-id db101))))
      (assert (= (get-grade (resolve-1 joe grade)) 4)))

    (let* ((sicp (match-1 'course :name "6.001"))
           (jane (student "Jane"))
           (grade (make-instance 'course-grade
                                 :course-id (get-id sicp))))

      (assert (= (get-grade (resolve-1 jane grade)) 1)))


    (crud-delete-from-instance *crud* (student "Joe"))
    (assert (= (length (crud-select 
                        *crud* (make-select :table "student")))
               2))))

(test-populate-crud)
(format t "Ok.~%")
(autoproject::quit)
